﻿Public Class VACCINECLASS
    Private VACCINEID As Integer = Nothing
    Private VACCODE As String = Nothing
    Private VACCINE_EN As String = Nothing
    Private VACCINE_TH As String = Nothing
    Private PRDCODE As Integer = Nothing
    Private AGE_M As Integer = Nothing
    Private REMARK As String = Nothing
    Private STATUS = Nothing
    Private F_VIP = Nothing
    Private SelectVaccine = Nothing
    Dim connect As CONDB = CONDB.NewConnection()
    Property SelectVaccine_() As String
        Get
            SelectVaccine = "SELECT `vaccineid`,`vaccode` as 'รหัสวัคซีน',`vaccine_en` as 'ชื่อสากล',`vaccine_th` as 'ชื่อไทย',drugitem.`prdcode`,drugitem.`genericname` as 'รหัสยา',`age_m` as 'อายุเดือน',`remark` as 'หมายเหตุ',masvaccine.`status` as 'สถานะการเปิดใช้งาน',CASE WHEN (`f_vip` IS NULL) THEN 0 ELSE 1 END as f_vip "
            SelectVaccine += " FROM masvaccine "
            SelectVaccine += " LEFT JOIN (SELECT `prdcode`,`drgcode`,`genericname` FROM drugitem WHERE `status` = 1) AS drugitem on masvaccine.`prdcode` = drugitem.`prdcode`;"
            Return SelectVaccine
        End Get
        Set(value As String)

        End Set
    End Property

    Property F_VIP_() As Boolean
        Get
            Return F_VIP
        End Get
        Set(value As Boolean)
            If value = True Then
                F_VIP = 1
            Else
                F_VIP = 0
            End If
        End Set
    End Property

    Property STATUS_() As Boolean
        Get
            Return STATUS
        End Get
        Set(value As Boolean)
            If value = True Then
                STATUS = 1
            Else
                STATUS = 0
            End If
        End Set
    End Property

    Property REMARK_() As String
        Get
            Return REMARK
        End Get
        Set(value As String)
            REMARK = value
        End Set
    End Property

    Property AGE_M_() As Integer
        Get
            Return AGE_M
        End Get
        Set(value As Integer)
            AGE_M = value
        End Set
    End Property

    Property PRDCODE_() As Integer
        Get
            Return PRDCODE
        End Get
        Set(value As Integer)
            PRDCODE = value
        End Set
    End Property

    Property VACCINE_TH_() As String
        Get
            Return VACCINE_TH
        End Get
        Set(value As String)
            VACCINE_TH = value
        End Set
    End Property

    Property VACCINE_EN_() As String
        Get
            Return VACCINE_EN
        End Get
        Set(value As String)
            VACCINE_EN = value
        End Set
    End Property

    Property VACCODE_() As String
        Get
            Return VACCODE
        End Get
        Set(value As String)
            VACCODE = value
        End Set
    End Property

    Property VACCINEID_() As Integer
        Get
            Return VACCINEID
        End Get
        Set(value As Integer)
            VACCINEID = value
        End Set
    End Property

    Public Function insertVaccineData(dgv As DevComponents.DotNetBar.SuperGrid.SuperGridControl, ByVal prdcodeLength As Integer, ByVal agemLength As Integer) As Boolean
        connect = CONDB.NewConnection()

        If connect.GetTable("SELECT vaccineid FROM masvaccine WHERE vaccode = '" & VACCODE & "' AND vaccine_en = '" & VACCINE_EN & "' AND vaccine_th = '" & VACCINE_TH & "'").Rows.Count > 0 Then
            MsgBox("ไม่สามารถเพิ่มรายชื่อวัคซีนนี้ได้ เนื่องจากมีวัคซีนนี้อยู่ในระบบอยู่แล้ว")
        Else
            Try
                connect.BeginTrans()
                'connectlocal = conlocal.NewConnection()
                Dim lastid As Integer
                Dim Sql As String
                Sql = "INSERT INTO masvaccine "
                Sql += "(`vaccode`,`vaccine_en`,`vaccine_th`,`prdcode`,`age_m`,`remark`,`f_vip`) "
                Sql += " VALUES ("
                Sql += "'" & VACCODE & "',"
                Sql += "'" & VACCINE_EN & "',"
                Sql += "'" & VACCINE_TH & "',"

                If prdcodeLength = 0 Then
                    Sql += "null,"
                Else
                    Sql += "'" & PRDCODE & "',"
                End If

                If agemLength = 0 Then
                    Sql += "null,"
                Else
                    Sql += "'" & AGE_M & "',"
                End If

                Sql += "'" & REMARK & "',"
                Sql += "'" & F_VIP & "'); SELECT LAST_INSERT_ID();"
                lastid = connect.ExecuteScalar(Sql)

                If dgv.PrimaryGrid.Rows.Count > 1 Then
                    Sql = ""
                    For i As Integer = 0 To dgv.PrimaryGrid.Rows.Count - 1
                        If CType(dgv.PrimaryGrid.Rows(i), DevComponents.DotNetBar.SuperGrid.GridRow).Cells("icdid").Value.ToString.Trim <> "" Then
                            Sql += "INSERT INTO masvaccineicd "
                            Sql += "(`vaccineid`,`icdid`)"
                            Sql += "VALUES ("
                            Sql += "'" & lastid & "',"
                            Sql += "'" & CType(dgv.PrimaryGrid.Rows(i), DevComponents.DotNetBar.SuperGrid.GridRow).Cells("icdid").Value & "');"
                        End If
                    Next i

                    connect.ExecuteNonQuery(Sql)
                End If

                connect.CommitTrans()
                connect.Dispose()
                Return True
            Catch ex As Exception
                connect.RollbackTrans()
                connect.Dispose()
                MsgBox(ex.ToString)
                connect.Dispose()
                Return False
            End Try
        End If
    End Function

    Public Function editVaccinedata(dgv As DevComponents.DotNetBar.SuperGrid.SuperGridControl, ByVal prdcodeLength As Integer, ByVal agemLength As Integer) As Boolean
        connect = CONDB.NewConnection()

        Try
            connect.BeginTrans()
            Dim sql As String
            sql = "UPDATE masvaccine SET "
            sql += "`vaccode` = '" & VACCODE & "',"
            sql += "`vaccine_en` = '" & VACCINE_EN & "',"
            sql += "`vaccine_th` = '" & VACCINE_TH & "',"

            If agemLength = 0 Then
                sql += "`age_m` = null,"
            Else
                sql += "`age_m` = '" & AGE_M & "',"
            End If

            If prdcodeLength = 0 Then
                sql += "`prdcode` = null,"
            Else
                sql += "`prdcode` = '" & PRDCODE & "',"
            End If

            sql += "`remark` = '" & REMARK & "',"
            If F_VIP Is Nothing Then
                sql += "`f_vip` = Null "
            Else
                sql += "`f_vip` = '" & F_VIP & "' "
            End If
            sql += "WHERE `vaccineid` = '" & VACCINEID & "'"
            connect.ExecuteNonQuery(sql)

            If dgv.PrimaryGrid.Rows.Count > 1 Then
                sql = ""
                For i As Integer = 0 To dgv.PrimaryGrid.Rows.Count - 1
                    'If CType(dgv.PrimaryGrid.Rows(i), DevComponents.DotNetBar.SuperGrid.GridRow).Cells("icdid").Value.ToString.Trim.Length > 0 Then
                    If CType(dgv.PrimaryGrid.Rows(i), DevComponents.DotNetBar.SuperGrid.GridRow).Cells("icdid").Value.ToString <> Nothing Then
                        If connect.GetTable("SELECT vicdid FROM masvaccineicd WHERE vicdid = '" & CType(dgv.PrimaryGrid.Rows(i), DevComponents.DotNetBar.SuperGrid.GridRow).Cells("vicdid").Value & "'").Rows.Count = 0 Then
                            sql += "INSERT INTO masvaccineicd "
                            sql += "(`vaccineid`,`icdid`)"
                            sql += "VALUES ("
                            sql += "'" & VACCINEID & "',"
                            sql += "'" & CType(dgv.PrimaryGrid.Rows(i), DevComponents.DotNetBar.SuperGrid.GridRow).Cells("icdid").Value & "');"
                        ElseIf connect.GetTable("SELECT vicdid FROM masvaccineicd WHERE vicdid = '" & CType(dgv.PrimaryGrid.Rows(i), DevComponents.DotNetBar.SuperGrid.GridRow).Cells("vicdid").Value & "'").Rows.Count > 0 Then
                            sql += "UPDATE masvaccineicd SET vaccineid = '" & VACCINEID & "', icdid = '" & CType(dgv.PrimaryGrid.Rows(i), DevComponents.DotNetBar.SuperGrid.GridRow).Cells("icdid").Value & "' WHERE vicdid = '" & CType(dgv.PrimaryGrid.Rows(i), DevComponents.DotNetBar.SuperGrid.GridRow).Cells("vicdid").Value & "';"
                        End If
                    End If
                Next i

                connect.ExecuteNonQuery(sql)
            End If

            connect.CommitTrans()

            connect.Dispose()
            Return True
        Catch ex As Exception
            connect.RollbackTrans()
            MsgBox(ex.ToString)
            connect.Dispose()
            Return False
        End Try
    End Function
End Class
